Load Libraries
require(tidyverse)
## Loading required package: tidyverse
## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --
## v ggplot2 3.3.5 v purrr 0.3.4
## v tibble 3.1.5 v dplyr 1.0.7
## v tidyr 1.1.4 v stringr 1.4.0
## v readr 2.0.2 v forcats 0.5.1
## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()
require(lubridate)
## Loading required package: lubridate
##
## Attaching package: 'lubridate'
## The following objects are masked from 'package:base':
##
## date, intersect, setdiff, union
require(stringr)
require(readxl)
## Loading required package: readxl
require(plotly)
## Loading required package: plotly
##
## Attaching package: 'plotly'
## The following object is masked from 'package:ggplot2':
##
## last_plot
## The following object is masked from 'package:stats':
##
## filter
## The following object is masked from 'package:graphics':
##
## layout
Load Call Trends Yearly files
CEMonthly_2019 <- read.csv("../Data/Cleaned/CEMonthly_2019.csv")
CEMonthly_2020 <- read.csv("../Data/Cleaned/CEMonthly_2020.csv")
CEMonthly_2021 <- read.csv("../Data/Cleaned/CEMonthly_2021.csv")
Type Covert
CEMonthly_2019 <- CEMonthly_2019 %>% mutate_all(type.convert)
## Warning in type.convert.default(DATE): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(CALLS): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(AGENT.COUNT): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(REPORTING.ABANDON_DISPOSITION.COUNT): 'as.is'
## should be specified by the caller; using TRUE
## Warning in type.convert.default(ABANDONED.COUNT): 'as.is' should be specified by
## the caller; using TRUE
## Warning in type.convert.default(ABANDONED.REC): 'as.is' should be specified by
## the caller; using TRUE
## Warning in type.convert.default(AVERAGE.HANDLE.TIME): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.SPEED.OF.ANSWER): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.QUEUE.WAIT.TIME): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.HOLD.TIME): 'as.is' should be specified
## by the caller; using TRUE
## Warning in type.convert.default(SERVICE.LEVEL.REC): 'as.is' should be specified
## by the caller; using TRUE
## Warning in type.convert.default(FILE_NAME): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(SHEET_NAME): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(CALL_TYPE): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(LANGUAGE): 'as.is' should be specified by the
## caller; using TRUE
CEMonthly_2019$DATE <- ymd(CEMonthly_2019$DATE)
CEMonthly_2020 <- CEMonthly_2020 %>% mutate_all(type.convert)
## Warning in type.convert.default(DATE): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(CALLS): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(AGENT.COUNT): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(REPORTING.ABANDON_DISPOSITION.COUNT): 'as.is'
## should be specified by the caller; using TRUE
## Warning in type.convert.default(ABANDONED.COUNT): 'as.is' should be specified by
## the caller; using TRUE
## Warning in type.convert.default(ABANDONED.REC): 'as.is' should be specified by
## the caller; using TRUE
## Warning in type.convert.default(AVERAGE.HANDLE.TIME): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.SPEED.OF.ANSWER): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.QUEUE.WAIT.TIME): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.HOLD.TIME): 'as.is' should be specified
## by the caller; using TRUE
## Warning in type.convert.default(SERVICE.LEVEL.REC): 'as.is' should be specified
## by the caller; using TRUE
## Warning in type.convert.default(FILE_NAME): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(SHEET_NAME): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(CALL_TYPE): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(LANGUAGE): 'as.is' should be specified by the
## caller; using TRUE
CEMonthly_2020$DATE <- ymd(CEMonthly_2020$DATE)
CEMonthly_2021 <- CEMonthly_2021 %>% mutate_all(type.convert)
## Warning in type.convert.default(DATE): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(CALLS): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(AGENT.COUNT): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(REPORTING.ABANDON_DISPOSITION.COUNT): 'as.is'
## should be specified by the caller; using TRUE
## Warning in type.convert.default(ABANDONED.COUNT): 'as.is' should be specified by
## the caller; using TRUE
## Warning in type.convert.default(ABANDONED.REC): 'as.is' should be specified by
## the caller; using TRUE
## Warning in type.convert.default(AVERAGE.HANDLE.TIME): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.SPEED.OF.ANSWER): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.QUEUE.WAIT.TIME): 'as.is' should be
## specified by the caller; using TRUE
## Warning in type.convert.default(AVERAGE.HOLD.TIME): 'as.is' should be specified
## by the caller; using TRUE
## Warning in type.convert.default(SERVICE.LEVEL.REC): 'as.is' should be specified
## by the caller; using TRUE
## Warning in type.convert.default(FILE_NAME): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(SHEET_NAME): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(CALL_TYPE): 'as.is' should be specified by the
## caller; using TRUE
## Warning in type.convert.default(LANGUAGE): 'as.is' should be specified by the
## caller; using TRUE
CEMonthly_2021$DATE <- ymd(CEMonthly_2021$DATE)
Align columns by names
CEMonthly_2020 <- CEMonthly_2020 %>% select(colnames(CEMonthly_2019))
CEMonthly_2021 <- CEMonthly_2021 %>% select(colnames(CEMonthly_2019))
Bind all Dataframes
CEMonthly <- rbind(CEMonthly_2019,CEMonthly_2020,CEMonthly_2021)
Convert wait times to seconds
CEMonthly <- CEMonthly %>% mutate(AVERAGE.HANDLE.TIME = if_else(is.na(AVERAGE.HANDLE.TIME),"00:00:00",AVERAGE.HANDLE.TIME)
,AVERAGE.SPEED.OF.ANSWER = if_else(is.na(AVERAGE.SPEED.OF.ANSWER),"00:00:00",AVERAGE.SPEED.OF.ANSWER)
,AVERAGE.QUEUE.WAIT.TIME = if_else(is.na(AVERAGE.QUEUE.WAIT.TIME),"00:00:00",AVERAGE.QUEUE.WAIT.TIME)
,AVERAGE.HOLD.TIME = if_else(is.na(AVERAGE.HOLD.TIME),"00:00:00",AVERAGE.HOLD.TIME))
CEMonthly <- CEMonthly %>% mutate(AVERAGE.HANDLE.TIME = if_else(AVERAGE.HANDLE.TIME == '-',"00:00:00",AVERAGE.HANDLE.TIME)
,AVERAGE.SPEED.OF.ANSWER = if_else(AVERAGE.SPEED.OF.ANSWER == '-',"00:00:00",AVERAGE.SPEED.OF.ANSWER)
,AVERAGE.QUEUE.WAIT.TIME = if_else(AVERAGE.QUEUE.WAIT.TIME == '-',"00:00:00",AVERAGE.QUEUE.WAIT.TIME)
,AVERAGE.HOLD.TIME = if_else(AVERAGE.HOLD.TIME == '-',"00:00:00",AVERAGE.HOLD.TIME))
CEMonthly <- CEMonthly %>% mutate(AVERAGE.HANDLE.TIME = as.integer(as.difftime(AVERAGE.HANDLE.TIME))
,AVERAGE.SPEED.OF.ANSWER = as.integer(as.difftime(AVERAGE.SPEED.OF.ANSWER))
,AVERAGE.QUEUE.WAIT.TIME = as.integer(as.difftime(AVERAGE.QUEUE.WAIT.TIME))
,AVERAGE.HOLD.TIME = as.integer(as.difftime(AVERAGE.HOLD.TIME)))
Make dataframe ready for plot
CEMonthly <- CEMonthly %>% mutate(MONTH = month(DATE),YEAR = year(DATE))
CEMonthly_Avg <- CEMonthly %>% group_by(YEAR,MONTH) %>% summarise(AVERAGE.QUEUE.WAIT.TIME = mean(AVERAGE.QUEUE.WAIT.TIME)
,AVERAGE.HOLD.TIME = mean(AVERAGE.HOLD.TIME)) %>% ungroup()
## `summarise()` has grouped output by 'YEAR'. You can override using the `.groups` argument.
CEMonthly_plot <- CEMonthly %>% select(MONTH) %>% distinct() %>% left_join(CEMonthly_Avg %>% filter(YEAR == 2019) %>% select(MONTH,AVERAGE.QUEUE.WAIT.TIME_2019 = AVERAGE.QUEUE.WAIT.TIME, AVERAGE.HOLD.TIME_2019 = AVERAGE.HOLD.TIME),by = 'MONTH') %>% left_join(CEMonthly_Avg %>% filter(YEAR == 2020) %>% select(MONTH,AVERAGE.QUEUE.WAIT.TIME_2020 = AVERAGE.QUEUE.WAIT.TIME, AVERAGE.HOLD.TIME_2020 = AVERAGE.HOLD.TIME),by = 'MONTH') %>% left_join(CEMonthly_Avg %>% filter(YEAR == 2021) %>% select(MONTH,AVERAGE.QUEUE.WAIT.TIME_2021 = AVERAGE.QUEUE.WAIT.TIME, AVERAGE.HOLD.TIME_2021 = AVERAGE.HOLD.TIME),by = 'MONTH')
CEMonthly_plot <- CEMonthly_plot %>% mutate(MONTH__TXT = month.abb[MONTH]) %>% arrange(MONTH)
CEMonthly_plot$MONTH__TXT <- factor(CEMonthly_plot$MONTH__TXT, levels = CEMonthly_plot[["MONTH__TXT"]])
Plot - Average Wait times
fig <- plot_ly(CEMonthly_plot, x = ~MONTH__TXT, y = ~AVERAGE.QUEUE.WAIT.TIME_2019, name = 'AVERAGE.QUEUE.WAIT.TIME_2019', type = 'scatter', mode = 'lines',line = list(color = 'rgb(205, 12, 24)', width = 4))
fig <- fig %>% add_trace(y = ~AVERAGE.QUEUE.WAIT.TIME_2020, name = 'AVERAGE.QUEUE.WAIT.TIME_2020', type = 'scatter', mode = 'lines', line = list(color = 'rgb(12, 205, 24)', width = 4))
fig <- fig %>% add_trace(y = ~AVERAGE.QUEUE.WAIT.TIME_2021, name = 'AVERAGE.QUEUE.WAIT.TIME_2021', type = 'scatter', mode = 'lines', line = list(color = 'rgb(24, 12, 205)', width = 4))
fig <- fig %>% layout(title = "Average Queue wait times",
xaxis = list(title = "Months"),
yaxis = list (title = "Time in seconds"))
fig
subplot(
plot_ly(CEMonthly_plot, x = ~MONTH__TXT, y = ~AVERAGE.QUEUE.WAIT.TIME_2019, name = 'AVERAGE.QUEUE.WAIT.TIME_2019', type = 'scatter', mode = 'lines',line = list(color = 'rgb(205, 12, 24)', width = 4)),
plot_ly(CEMonthly_plot, x = ~MONTH__TXT,y = ~AVERAGE.QUEUE.WAIT.TIME_2020, name = 'AVERAGE.QUEUE.WAIT.TIME_2020', type = 'scatter', mode = 'lines', line = list(color = 'rgb(12, 205, 24)', width = 4))
, plot_ly(CEMonthly_plot, x = ~MONTH__TXT,y = ~AVERAGE.QUEUE.WAIT.TIME_2021, name = 'AVERAGE.QUEUE.WAIT.TIME_2021', type = 'scatter', mode = 'lines', line = list(color = 'rgb(24, 12, 205)', width = 4)) %>%
layout(title = "Average Queue Wait Times",
xaxis = list(title = "Months"),
yaxis = list (title = "Time in seconds")),shareX = TRUE, shareY = TRUE)
Plot - Average hold times
fig <- plot_ly(CEMonthly_plot, x = ~MONTH__TXT, y = ~AVERAGE.HOLD.TIME_2019, name = 'AVERAGE.HOLD.TIME_2019', type = 'scatter', mode = 'lines',line = list(color = 'rgb(205, 12, 24)', width = 4))
fig <- fig %>% add_trace(y = ~AVERAGE.HOLD.TIME_2020, name = 'AVERAGE.HOLD.TIME_2020', type = 'scatter', mode = 'lines', line = list(color = 'rgb(12, 205, 24)', width = 4))
fig <- fig %>% add_trace(y = ~AVERAGE.HOLD.TIME_2021, name = 'AVERAGE.HOLD.TIME_2021', type = 'scatter', mode = 'lines', line = list(color = 'rgb(24, 12, 205)', width = 4))
fig <- fig %>% layout(title = "Average hold times",
xaxis = list(title = "Months"),
yaxis = list (title = "Time in seconds"))
fig
subplot(
plot_ly(CEMonthly_plot, x = ~MONTH__TXT, y = ~AVERAGE.HOLD.TIME_2019, name = 'AVERAGE.HOLD.TIME_2019', type = 'scatter', mode = 'lines',line = list(color = 'rgb(205, 12, 24)', width = 4)),
plot_ly(CEMonthly_plot, x = ~MONTH__TXT,y = ~AVERAGE.HOLD.TIME_2020, name = 'AVERAGE.HOLD.TIME_2020', type = 'scatter', mode = 'lines', line = list(color = 'rgb(12, 205, 24)', width = 4))
, plot_ly(CEMonthly_plot, x = ~MONTH__TXT,y = ~AVERAGE.HOLD.TIME_2021, name = 'AVERAGE.HOLD.TIME_2020', type = 'scatter', mode = 'lines', line = list(color = 'rgb(24, 12, 205)', width = 4)) %>%
layout(title = "Average hold times",
xaxis = list(title = "Months"),
yaxis = list (title = "Time in seconds")),shareX = TRUE, shareY = TRUE)